
-- --------------------------------------------------
-- Entity Designer DDL Script for SQL Server 2005, 2008, 2012 and Azure
-- --------------------------------------------------
-- Date Created: 04/08/2014 10:12:41
-- Generated from EDMX file: C:\Users\Alexander Mauermann\Documents\Visual Studio 2013\Projects\entityframework_laboration3_revised\EFDAL\WebsiteModel.edmx
-- --------------------------------------------------

SET QUOTED_IDENTIFIER OFF;
GO
USE [entityframework_lab3];
GO
IF SCHEMA_ID(N'dbo') IS NULL EXECUTE(N'CREATE SCHEMA [dbo]');
GO

-- --------------------------------------------------
-- Dropping existing FOREIGN KEY constraints
-- --------------------------------------------------

IF OBJECT_ID(N'[dbo].[FK_AccountWorkAds]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[WorkAdsSets] DROP CONSTRAINT [FK_AccountWorkAds];
GO
IF OBJECT_ID(N'[dbo].[FK_ContactInformationAccount]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[ContactInformationSets] DROP CONSTRAINT [FK_ContactInformationAccount];
GO
IF OBJECT_ID(N'[dbo].[FK_CVAccount]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[CVSets] DROP CONSTRAINT [FK_CVAccount];
GO
IF OBJECT_ID(N'[dbo].[FK_CVJob]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[JobEntrySets] DROP CONSTRAINT [FK_CVJob];
GO
IF OBJECT_ID(N'[dbo].[FK_CVMerit]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[MeritSets] DROP CONSTRAINT [FK_CVMerit];
GO

-- --------------------------------------------------
-- Dropping existing tables
-- --------------------------------------------------

IF OBJECT_ID(N'[dbo].[AccountSets]', 'U') IS NOT NULL
    DROP TABLE [dbo].[AccountSets];
GO
IF OBJECT_ID(N'[dbo].[ContactInformationSets]', 'U') IS NOT NULL
    DROP TABLE [dbo].[ContactInformationSets];
GO
IF OBJECT_ID(N'[dbo].[CVSets]', 'U') IS NOT NULL
    DROP TABLE [dbo].[CVSets];
GO
IF OBJECT_ID(N'[dbo].[JobEntrySets]', 'U') IS NOT NULL
    DROP TABLE [dbo].[JobEntrySets];
GO
IF OBJECT_ID(N'[dbo].[MeritSets]', 'U') IS NOT NULL
    DROP TABLE [dbo].[MeritSets];
GO
IF OBJECT_ID(N'[dbo].[WorkAdsSets]', 'U') IS NOT NULL
    DROP TABLE [dbo].[WorkAdsSets];
GO

-- --------------------------------------------------
-- Creating all tables
-- --------------------------------------------------

-- Creating table 'AccountSets'
CREATE TABLE [dbo].[AccountSets] (
    [AccountId] int IDENTITY(1,1) NOT NULL,
    [Authority] int  NOT NULL,
    [Email] nvarchar(max)  NOT NULL,
    [Password] nvarchar(max)  NOT NULL
);
GO

-- Creating table 'ContactInformationSets'
CREATE TABLE [dbo].[ContactInformationSets] (
    [ContactInformationId] int IDENTITY(1,1) NOT NULL,
    [CompleteName] nvarchar(max)  NOT NULL,
    [PhoneNumber] nvarchar(max)  NOT NULL,
    [AccountId] int  NOT NULL
);
GO

-- Creating table 'CVSets'
CREATE TABLE [dbo].[CVSets] (
    [CVId] int IDENTITY(1,1) NOT NULL,
    [CVText] nvarchar(max)  NOT NULL,
    [AccountId] int  NOT NULL
);
GO

-- Creating table 'JobEntrySets'
CREATE TABLE [dbo].[JobEntrySets] (
    [JobId] int IDENTITY(1,1) NOT NULL,
    [StartDate] datetime  NOT NULL,
    [EndDate] datetime  NULL,
    [Deleted] bit  NOT NULL,
    [Title] nvarchar(max)  NOT NULL,
    [Description] nvarchar(max)  NOT NULL,
    [CVId] int  NOT NULL
);
GO

-- Creating table 'MeritSets'
CREATE TABLE [dbo].[MeritSets] (
    [MeritId] int IDENTITY(1,1) NOT NULL,
    [Title] int  NOT NULL,
    [Level] int  NOT NULL,
    [CVId] int  NOT NULL
);
GO

-- Creating table 'WorkAdsSets'
CREATE TABLE [dbo].[WorkAdsSets] (
    [WorkId] int IDENTITY(1,1) NOT NULL,
    [EnteredDate] datetime  NOT NULL,
    [ExpiredDate] datetime  NOT NULL,
    [Deleted] bit  NOT NULL,
    [Filled] bit  NOT NULL,
    [Title] nvarchar(max)  NOT NULL,
    [Description] nvarchar(max)  NOT NULL,
    [AccountId] int  NOT NULL
);
GO

-- --------------------------------------------------
-- Creating all PRIMARY KEY constraints
-- --------------------------------------------------

-- Creating primary key on [AccountId] in table 'AccountSets'
ALTER TABLE [dbo].[AccountSets]
ADD CONSTRAINT [PK_AccountSets]
    PRIMARY KEY CLUSTERED ([AccountId] ASC);
GO

-- Creating primary key on [ContactInformationId] in table 'ContactInformationSets'
ALTER TABLE [dbo].[ContactInformationSets]
ADD CONSTRAINT [PK_ContactInformationSets]
    PRIMARY KEY CLUSTERED ([ContactInformationId] ASC);
GO

-- Creating primary key on [CVId] in table 'CVSets'
ALTER TABLE [dbo].[CVSets]
ADD CONSTRAINT [PK_CVSets]
    PRIMARY KEY CLUSTERED ([CVId] ASC);
GO

-- Creating primary key on [JobId] in table 'JobEntrySets'
ALTER TABLE [dbo].[JobEntrySets]
ADD CONSTRAINT [PK_JobEntrySets]
    PRIMARY KEY CLUSTERED ([JobId] ASC);
GO

-- Creating primary key on [MeritId] in table 'MeritSets'
ALTER TABLE [dbo].[MeritSets]
ADD CONSTRAINT [PK_MeritSets]
    PRIMARY KEY CLUSTERED ([MeritId] ASC);
GO

-- Creating primary key on [WorkId] in table 'WorkAdsSets'
ALTER TABLE [dbo].[WorkAdsSets]
ADD CONSTRAINT [PK_WorkAdsSets]
    PRIMARY KEY CLUSTERED ([WorkId] ASC);
GO

-- --------------------------------------------------
-- Creating all FOREIGN KEY constraints
-- --------------------------------------------------

-- Creating foreign key on [AccountId] in table 'WorkAdsSets'
ALTER TABLE [dbo].[WorkAdsSets]
ADD CONSTRAINT [FK_AccountWorkAds]
    FOREIGN KEY ([AccountId])
    REFERENCES [dbo].[AccountSets]
        ([AccountId])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_AccountWorkAds'
CREATE INDEX [IX_FK_AccountWorkAds]
ON [dbo].[WorkAdsSets]
    ([AccountId]);
GO

-- Creating foreign key on [AccountId] in table 'ContactInformationSets'
ALTER TABLE [dbo].[ContactInformationSets]
ADD CONSTRAINT [FK_ContactInformationAccount]
    FOREIGN KEY ([AccountId])
    REFERENCES [dbo].[AccountSets]
        ([AccountId])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_ContactInformationAccount'
CREATE INDEX [IX_FK_ContactInformationAccount]
ON [dbo].[ContactInformationSets]
    ([AccountId]);
GO

-- Creating foreign key on [AccountId] in table 'CVSets'
ALTER TABLE [dbo].[CVSets]
ADD CONSTRAINT [FK_CVAccount]
    FOREIGN KEY ([AccountId])
    REFERENCES [dbo].[AccountSets]
        ([AccountId])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_CVAccount'
CREATE INDEX [IX_FK_CVAccount]
ON [dbo].[CVSets]
    ([AccountId]);
GO

-- Creating foreign key on [CVId] in table 'JobEntrySets'
ALTER TABLE [dbo].[JobEntrySets]
ADD CONSTRAINT [FK_CVJob]
    FOREIGN KEY ([CVId])
    REFERENCES [dbo].[CVSets]
        ([CVId])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_CVJob'
CREATE INDEX [IX_FK_CVJob]
ON [dbo].[JobEntrySets]
    ([CVId]);
GO

-- Creating foreign key on [CVId] in table 'MeritSets'
ALTER TABLE [dbo].[MeritSets]
ADD CONSTRAINT [FK_CVMerit]
    FOREIGN KEY ([CVId])
    REFERENCES [dbo].[CVSets]
        ([CVId])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_CVMerit'
CREATE INDEX [IX_FK_CVMerit]
ON [dbo].[MeritSets]
    ([CVId]);
GO

-- --------------------------------------------------
-- Script has ended
-- --------------------------------------------------